package com.edu.hre.db.sql.recruit;

import java.io.Serializable;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.edu.hre.common.utils.CommonUtils;
import com.edu.hre.db.pojo.HreJobInterview;
import com.edu.hre.db.service.BaseService;
import com.edu.hre.db.utils.Compare;
import com.edu.hre.struts.form.recruit.InterviewForm;
import com.edu.hre.struts.form.recruit.InterviewListForm;

public class InterviewDAOService implements Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 3394301416150421253L;
	public static final String BEAN_NAME = "interviewDAOService";
	private BaseService baseService  = null;

	public void setBaseService(BaseService baseService) {
		this.baseService = baseService;
	} 

	public List<InterviewForm> getListInterviewForm(InterviewListForm form, int limit, int offset){
		
		List<InterviewForm> listResult = new ArrayList<InterviewForm>();
		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isCan = form.getSelectedCandidate() != null && !"".equals(form.getSelectedCandidate())
				&& !"0".equals(form.getSelectedCandidate());
		
		boolean isVac = form.getSelectedVacancy() != null && !"".equals(form.getSelectedVacancy())
				&& !"0".equals(form.getSelectedVacancy());

		boolean isName = form.getNameKeyWord() != null && !"".equals(form.getNameKeyWord())
				&& !"0".equals(form.getNameKeyWord());
		
		boolean isInterviewer = form.getInterviewer() != null && !"".equals(form.getInterviewer())
				&& !"0".equals(form.getInterviewer());

		sql = "SELECT jobint.id AS col1," +
					" CONCAT(can.last_name, ' ', can.middle_name, ' ', can.first_name)  AS col2, " +
					" jobint.interview_name AS col3, " +
					" jobint.candidate_id AS col4, " +
					" jobint.vacancy_id AS col5, " +
					" vac.name AS col6, " +
					" jobint.interview_time AS col7, " +
					" jobint.status AS col8, " +
					" jobint.result AS col9, " +
					" CONCAT(emp1.emp_lastname, ' ', emp1.emp_middle_name, ' ', emp1.emp_firstname) AS col10, " +
					" CONCAT(emp2.emp_lastname, ' ', emp2.emp_middle_name, ' ', emp2.emp_firstname) AS col11, " +
					" CONCAT(emp3.emp_lastname, ' ', emp3.emp_middle_name, ' ', emp3.emp_firstname) AS col12, " +
					" CONCAT(emp4.emp_lastname, ' ', emp4.emp_middle_name, ' ', emp4.emp_firstname) AS col13, " +
					" CONCAT(emp5.emp_lastname, ' ', emp5.emp_middle_name, ' ', emp5.emp_firstname) AS col14 " +
					
					" FROM hre_job_interview AS jobint " +
					" LEFT OUTER JOIN hre_job_candidate AS can ON jobint.candidate_id = can.id " +
					" LEFT OUTER JOIN hre_job_vacancy AS vac ON jobint.vacancy_id = vac.id " +
					" LEFT OUTER JOIN hre_employee AS emp1 ON jobint.interviewer_1 = emp1.emp_number " +
					" LEFT OUTER JOIN hre_employee AS emp2 ON jobint.interviewer_2 = emp2.emp_number " +
					" LEFT OUTER JOIN hre_employee AS emp3 ON jobint.interviewer_3 = emp3.emp_number " +
					" LEFT OUTER JOIN hre_employee AS emp4 ON jobint.interviewer_4 = emp4.emp_number " +
					" LEFT OUTER JOIN hre_employee AS emp5 ON jobint.interviewer_5 = emp5.emp_number ";
		
		sql +=	" WHERE 1 = 1 ";
		
		if (isCan) {
			sql +=	" AND can.id =  ? ";
			params.add(form.getSelectedCandidate());
		}
		if (isVac) {
			sql +=	" AND vac.id =  ? ";
			params.add(form.getSelectedVacancy());
		}
		if (isName) {
			sql +=	" AND jobint.interview_name LIKE '%" + form.getNameKeyWord() + "%' ";
		}
		if (isInterviewer) {
			sql +=	" AND (CONCAT(emp1.emp_lastname, ' ', emp1.emp_middle_name, ' ', emp1.emp_firstname, ' [', emp1.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp2.emp_lastname, ' ', emp2.emp_middle_name, ' ', emp2.emp_firstname, ' [', emp2.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp3.emp_lastname, ' ', emp3.emp_middle_name, ' ', emp3.emp_firstname, ' [', emp3.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp4.emp_lastname, ' ', emp4.emp_middle_name, ' ', emp4.emp_firstname, ' [', emp4.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp5.emp_lastname, ' ', emp5.emp_middle_name, ' ', emp5.emp_firstname, ' [', emp5.main_username, ']') LIKE '%" + form.getInterviewer() + "%') ";
		}

		sql += " ORDER BY jobint.date_created DESC";

		List <Object[]> listObject = baseService.listArrayObject(sql, limit, offset, params.toArray());
		for(int i = 0; listObject != null && i < listObject.size(); i++){
			
			try {

				Object[] objs = listObject.get(i);
				InterviewForm interview = new InterviewForm();

				interview.setId(CommonUtils.normalizeObjectToString(objs[1], "0"));
				interview.setCandidateName(CommonUtils.normalizeObjectToString(objs[2], ""));
				interview.setInterviewName(CommonUtils.normalizeObjectToString(objs[3], ""));
				interview.setCanId(Integer.parseInt(CommonUtils.normalizeObjectToString(objs[4], "0")));
				interview.setVacId(Integer.parseInt(CommonUtils.normalizeObjectToString(objs[5], "0")));
				interview.setVacancyName(CommonUtils.normalizeObjectToString(objs[6], ""));
				
				String timeStamp = "";
				String timeStr = CommonUtils.dateToString((Date) objs[7]);
				if (timeStr != null && !"".equals(timeStr)) {
					timeStamp += timeStr;
					
					if (objs[7] != null && objs[7] instanceof Date) {
						Date dat = (Date) objs[7];
						timeStamp += " " + CommonUtils.to2DigitalInt(dat.getHours());
						timeStamp += ":" + CommonUtils.to2DigitalInt(dat.getMinutes());
					}
				}
				interview.setInterviewTime(timeStamp);
				
				String[] statuses = new String[]{"Created", "Interviewed", "Closed"};
				try {
					int status = Integer.parseInt(CommonUtils.normalizeObjectToString(objs[8], "-1")) - 1;
					interview.setStatus(statuses[status]);
				} catch (Exception e) {

				}
				
				String[] results = new String[]{"Not Available", "Passed", "Not Passed"};
				try {
					int status = Integer.parseInt(CommonUtils.normalizeObjectToString(objs[9], "-1")) - 1;
					interview.setResult(results[status]);
				} catch (Exception e) {

				}
				
				String listInt = "";
				String ints[] = new String[5];
				ints[0] = CommonUtils.normalizeObjectToString(objs[10], "");
				ints[1] = CommonUtils.normalizeObjectToString(objs[11], "");
				ints[2] = CommonUtils.normalizeObjectToString(objs[12], "");
				ints[3] = CommonUtils.normalizeObjectToString(objs[13], "");
				ints[4] = CommonUtils.normalizeObjectToString(objs[14], "");
				
				for (int ind = 0; ind < 5; ind++) {
					if (!"".equalsIgnoreCase(ints[ind])) {
						listInt += ints[ind] + ", ";
					}
				}
				
				if (listInt.length() > 2) {
					listInt = listInt.substring(0, listInt.length() - 2);
				}
				
				interview.setInterviewers(listInt);

				listResult.add(interview);
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}

		return listResult;
	}
	
	public int countInterviewForm(InterviewListForm form){

		String sql = "";
		ArrayList<Object> params = new ArrayList<Object>();
		
		boolean isCan = form.getSelectedCandidate() != null && !"".equals(form.getSelectedCandidate())
				&& !"0".equals(form.getSelectedCandidate());
		
		boolean isVac = form.getSelectedVacancy() != null && !"".equals(form.getSelectedVacancy())
				&& !"0".equals(form.getSelectedVacancy());

		boolean isName = form.getNameKeyWord() != null && !"".equals(form.getNameKeyWord())
				&& !"0".equals(form.getNameKeyWord());
		
		boolean isInterviewer = form.getInterviewer() != null && !"".equals(form.getInterviewer())
				&& !"0".equals(form.getInterviewer());

		sql = "SELECT COUNT(*) AS col1" +
					
				" FROM hre_job_interview AS jobint " +
				" LEFT OUTER JOIN hre_job_candidate AS can ON jobint.candidate_id = can.id " +
				" LEFT OUTER JOIN hre_job_vacancy AS vac ON jobint.vacancy_id = vac.id " +
				" LEFT OUTER JOIN hre_employee AS emp1 ON jobint.interviewer_1 = emp1.emp_number " +
				" LEFT OUTER JOIN hre_employee AS emp2 ON jobint.interviewer_2 = emp2.emp_number " +
				" LEFT OUTER JOIN hre_employee AS emp3 ON jobint.interviewer_3 = emp3.emp_number " +
				" LEFT OUTER JOIN hre_employee AS emp4 ON jobint.interviewer_4 = emp4.emp_number " +
				" LEFT OUTER JOIN hre_employee AS emp5 ON jobint.interviewer_5 = emp5.emp_number ";
			
		sql +=	" WHERE 1 = 1 ";
		
		if (isCan) {
			sql +=	" AND can.id =  ? ";
			params.add(form.getSelectedCandidate());
		}
		if (isVac) {
			sql +=	" AND vac.id =  ? ";
			params.add(form.getSelectedVacancy());
		}
		if (isName) {
			sql +=	" AND jobint.interview_name LIKE '%" + form.getNameKeyWord() + "%' ";
		}
		if (isInterviewer) {
			sql +=	" AND (CONCAT(emp1.emp_lastname, ' ', emp1.emp_middle_name, ' ', emp1.emp_firstname, ' [', emp1.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp2.emp_lastname, ' ', emp2.emp_middle_name, ' ', emp2.emp_firstname, ' [', emp2.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp3.emp_lastname, ' ', emp3.emp_middle_name, ' ', emp3.emp_firstname, ' [', emp3.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp4.emp_lastname, ' ', emp4.emp_middle_name, ' ', emp4.emp_firstname, ' [', emp4.main_username, ']') LIKE '%" + form.getInterviewer() + "%' ";
			sql +=	" OR CONCAT(emp5.emp_lastname, ' ', emp5.emp_middle_name, ' ', emp5.emp_firstname, ' [', emp5.main_username, ']') LIKE '%" + form.getInterviewer() + "%') ";
		}

		try {
			List <Object[]> listObject = baseService.listArrayObject(sql, 0, 0, params.toArray());
			Object o [] = listObject.get(0);
	
			return ((BigInteger) o[1]).intValue();
		} catch (Exception e) {
			CommonUtils.println(e.getMessage());
			return 0;
		}
	}
	
	public void deleteInterviews(InterviewListForm form){

		ArrayList<InterviewForm> info = (ArrayList<InterviewForm>) form.getAllInterviews();
		for (int i = 0; i < info.size(); i++) {
			
			try {

				InterviewForm can = info.get(i);
				if (can.isSelected()) { 
	
					HreJobInterview tmp = baseService.getFirstPojo("id", Compare.EQUAL, Integer.parseInt(can.getId()), null, null, 0, HreJobInterview.class);
					baseService.deletePojo(tmp);
				}
			} catch (Exception e) {
				CommonUtils.println(e.getMessage());
			}
		}
	}
}
